﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using DaiKin.DBDataAccessLib.Helper;
using System.Data;
using System.Data.OracleClient;
using System.Data.SqlClient;

namespace DBDataAccessLib
{
    public abstract class StationNameDataAccess
    {
        public static List<string> GetAllStationNames(string wcno)
        {
            List<string> stationNames = new List<string>();
            DbDataReader reader;
            if (DBHelperFactory.DbName == DBType.Oracle)
                reader = OracleHelper.ExecuteReader(
                    OracleHelper.ConnectionStringLocalTransaction,
                    CommandType.Text,
                    SqlProvider.SelectStationNamesByWCNO,
                    new OracleParameter(":wcno", wcno)
                    );
            else
                reader = SqlHelper.ExecuteReader(
                    SqlHelper.ConnectionStringLocalTransaction,
                    CommandType.Text,
                    SqlProvider.SelectStationNamesByWCNO,
                    new SqlParameter("@wcno", wcno)
                    );
            while (reader.Read())
            {
                if (reader.GetValue(0) != DBNull.Value)
                    stationNames.Add(reader.GetString(0).Trim());
            }
            return stationNames;
        }

        public static int AddNewStation(string stationName)
        {
            int affectRows;
            if (DBHelperFactory.DbName == DBType.Oracle)
                affectRows = OracleHelper.ExecuteNonQuery(
                OracleHelper.ConnectionStringLocalTransaction,
                CommandType.Text,
                SqlProvider.AddStationName,
                new OracleParameter(":stationName", stationName)
                );
            else
                affectRows = SqlHelper.ExecuteNonQuery(
                    SqlHelper.ConnectionStringLocalTransaction,
                    CommandType.Text,
                    SqlProvider.AddStationName,
                    new SqlParameter("@stationName", stationName)
                    );
            return affectRows;
        }

        public static int DeleteStationName(object stationName, string wcno)
        {
            int affectRows;
            if (DBHelperFactory.DbName == DBType.Oracle)
                affectRows = OracleHelper.ExecuteNonQuery(
                OracleHelper.ConnectionStringLocalTransaction,
                CommandType.Text,
                SqlProvider.DeleteStationName,
                new OracleParameter(":stationName", stationName),
                new OracleParameter(":wcno", wcno)
                );
            else
                affectRows = SqlHelper.ExecuteNonQuery(
                SqlHelper.ConnectionStringLocalTransaction,
                CommandType.Text,
                SqlProvider.DeleteStationName,
                new SqlParameter("@stationName", stationName),
                new SqlParameter("@wcno", wcno)
                );
            return affectRows;
        }

        public static List<string> GetAllWCNOLines()
        {
            List<string> wcnoLines = new List<string>();
            DbDataReader reader;
            if (DBHelperFactory.DbName == DBType.Oracle)
                reader = OracleHelper.ExecuteReader(
                    OracleHelper.ConnectionStringLocalTransaction,
                    CommandType.Text,
                    SqlProvider.SelectAllWCNOLines
                    );
            else
                reader = SqlHelper.ExecuteReader(
                    SqlHelper.ConnectionStringLocalTransaction,
                    CommandType.Text,
                    SqlProvider.SelectAllWCNOLines
                    );
            while (reader.Read())
            {
                wcnoLines.Add(reader.GetString(0).Trim());
            }
            return wcnoLines;
        }

        public static int AddNewWCNO(string wcno)
        {
            return AddNewStation(DBNull.Value, wcno);
        }

        public static int AddNewStation(object stationName, string wcno)
        {
            int affectRows;
            //DeleteStationName(DBNull.Value, wcno);
            if (DBHelperFactory.DbName == DBType.Oracle)
                affectRows = OracleHelper.ExecuteNonQuery(
                OracleHelper.ConnectionStringLocalTransaction,
                CommandType.Text,
                SqlProvider.AddStationName,
                new OracleParameter(":stationName", stationName),
                new OracleParameter(":wcno", wcno)
                );
            else
                affectRows = SqlHelper.ExecuteNonQuery(
                    SqlHelper.ConnectionStringLocalTransaction,
                    CommandType.Text,
                    SqlProvider.AddStationName,
                    new SqlParameter("@stationName", stationName),
                    new SqlParameter("@wcno", wcno)
                    );
            return affectRows;
        }

        public static int DeleteWCNOLine(string wcno)
        {
            int affectRows;
            if (DBHelperFactory.DbName == DBType.Oracle)
                affectRows = OracleHelper.ExecuteNonQuery(
                OracleHelper.ConnectionStringLocalTransaction,
                CommandType.Text,
                SqlProvider.DeleteWCNOLine,
                new OracleParameter(":wcno", wcno)
                );
            else
                affectRows = SqlHelper.ExecuteNonQuery(
                SqlHelper.ConnectionStringLocalTransaction,
                CommandType.Text,
                SqlProvider.DeleteWCNOLine,
                new SqlParameter("@wcno", wcno)
                );
            return affectRows;
        }
    }
}
